package com.example.demo.service.impl;

import com.example.demo.config.DataSourceConfig;
import com.example.demo.entity.RiskL1SyncSqlConf;
import com.example.demo.entity.RiskL1SyncSqlEntity;
import com.example.demo.dto.SyncSqlDto;
import com.example.demo.mapper.RiskL1SyncSqlConfDao;
import com.example.demo.service.SyncSql;
import com.google.common.collect.Lists;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.*;

/**
 * ClassName:SyncSqlImpl
 * Package:com.example.demo.service.impl
 * TODO:
 *
 * @date:2021/8/28 11:26
 * @author:Vicky(陈烟星)
 */

@Service
public class SyncSqlImpl implements SyncSql {

    private static Logger logger = LoggerFactory.getLogger(SyncSqlImpl.class);

    @Autowired
    private RiskL1SyncSqlConfDao riskL1SyncSqlConfDao;
    @Autowired
    private DataSourceConfig dataSourceConfig;


    @Override
    public String appendSyncSql(String srcTableName, String tgtTableName) {
        if (StringUtils.isBlank(srcTableName) && StringUtils.isBlank(tgtTableName)) {
            return StringUtils.EMPTY;
        }
        RiskL1SyncSqlConf riskL1SyncSqlConf = new RiskL1SyncSqlConf();
        riskL1SyncSqlConf.setSrcTabName(srcTableName);
        riskL1SyncSqlConf.setTgtTabName(tgtTableName);
        RiskL1SyncSqlEntity riskL1SyncSqlEntity = riskL1SyncSqlConfDao.queryCondition(srcTableName, tgtTableName);
        if (riskL1SyncSqlEntity == null) {
            return StringUtils.EMPTY;
        }
        return appendSql(riskL1SyncSqlEntity);
    }

    private String appendSql(RiskL1SyncSqlEntity riskL1SyncSqlEntity) {
        StringBuilder builder = new StringBuilder();
        builder.append("insert into ");
        builder.append(riskL1SyncSqlEntity.getTargetTableName());
        builder.append("(");
        builder.append(riskL1SyncSqlEntity.getTargetTableFiled());
        builder.append(") ");
        builder.append("select ");
        builder.append(riskL1SyncSqlEntity.getSourceLoadCode());
        return builder.toString();
    }

    @Override
    public List<SyncSqlDto> appendAllSyncSql() {
        List<RiskL1SyncSqlEntity> riskL1SyncSqlEntityList = riskL1SyncSqlConfDao.queryAllCondition();
        if (CollectionUtils.isEmpty(riskL1SyncSqlEntityList)) {
            return Collections.EMPTY_LIST;
        }
        List<SyncSqlDto> list = Lists.newArrayListWithExpectedSize(riskL1SyncSqlEntityList.size());
        SyncSqlDto syncSqlDto = null;
        for (RiskL1SyncSqlEntity entity : riskL1SyncSqlEntityList) {
            syncSqlDto = new SyncSqlDto();
            syncSqlDto.setSrcTableName(entity.getSourceTableName());
            syncSqlDto.setTgtTableName(entity.getTargetTableName());
            syncSqlDto.setSyncSql(appendSql(entity));
            list.add(syncSqlDto);
        }
        return list;
    }

    /**
     * 查询由sql自己拼接的sql
     *
     * @param srcTableName
     * @param tgtTableName
     * @return
     */
    @Override
    public SyncSqlDto querySyncSql(String srcTableName, String tgtTableName) {
        RiskL1SyncSqlEntity riskL1SyncSqlEntity = riskL1SyncSqlConfDao.appendOneSql(srcTableName, tgtTableName);
        if (riskL1SyncSqlEntity != null) {
            SyncSqlDto syncSqlDto = new SyncSqlDto();
            syncSqlDto.setSyncSql(riskL1SyncSqlEntity.getAppendSql());
            syncSqlDto.setSrcTableName(riskL1SyncSqlEntity.getSourceTableName());
            syncSqlDto.setTgtTableName(riskL1SyncSqlEntity.getTargetTableName());
            return syncSqlDto;
        }
        return null;
    }

    /**
     * 查询由sql自己拼接的所有sql
     *
     * @return
     */
    @Override
    public List<SyncSqlDto> queryAllSyncSql() {
        List<RiskL1SyncSqlEntity> appendAllSqls = riskL1SyncSqlConfDao.appendAllSql();
        if (CollectionUtils.isEmpty(appendAllSqls)) {
            return Collections.EMPTY_LIST;
        }
        List<SyncSqlDto> list = Lists.newArrayListWithExpectedSize(appendAllSqls.size());
        SyncSqlDto syncSqlDto = null;
        for (RiskL1SyncSqlEntity entity : appendAllSqls) {
            syncSqlDto = new SyncSqlDto();
            syncSqlDto.setSrcTableName(entity.getSourceTableName());
            syncSqlDto.setTgtTableName(entity.getTargetTableName());
            syncSqlDto.setSyncSql(entity.getAppendSql());
            list.add(syncSqlDto);
        }
        return list;
    }


    /**
     * @param sourceTable source table name
     * @param sinkTable   sink table name
     * @return the unique DML SQL between source table and sink table
     */
    @Override
    public String genMigrateDML(String sourceTable, String sinkTable) {
        String queryMigrateRelationSQL = "SELECT T1.UNI_SEL_ID, T1.LOAD_COD, T2.FLD_COD, T2.ENG_FLD " +
                "FROM " +
                "(SELECT UNI_SEL_ID, LOAD_COD " +
                "FROM RISK_L1_SYNC_SQL_CONF " +
                "WHERE SRC_TAB_NAME = '" + sourceTable + "' AND TGT_TAB_NAME = '" + sinkTable + "') T1 " +
                "JOIN " +
                "(SELECT UNI_SEL_ID, FLD_COD, ENG_FLD " +
                "FROM RISK_L1_SYNC_SQL_FLD " +
                "WHERE SRC_TAB_NAME = '" + sourceTable + "' AND TGT_TAB_NAME = '" + sinkTable + "') T2 " +
                "ON T1.UNI_SEL_ID = T2.UNI_SEL_ID";
        String querySinkTableColumnsSQL = "SELECT COLUMN_NAME " +
                "FROM USER_TAB_COLUMNS " +
                "WHERE TABLE_NAME = '" + sinkTable.toUpperCase() + "' " +
                "ORDER BY COLUMN_ID";
        Connection connection = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            connection = DriverManager.getConnection(dataSourceConfig.getUrl(), dataSourceConfig.getUsername(), dataSourceConfig.getPassword());
            Statement statement = connection.createStatement();
            ResultSet sinkTableColumnRs = statement.executeQuery(querySinkTableColumnsSQL);
            ArrayList<String> sinkTableColumnList = new ArrayList<>();
            while (sinkTableColumnRs.next()) {
                sinkTableColumnList.add(sinkTableColumnRs.getString("COLUMN_NAME"));
            }
            ResultSet migrateRelationRs = statement.executeQuery(queryMigrateRelationSQL);
            Map<String, Map<String, String>> migrateConf = new HashMap<>();
            while (migrateRelationRs.next()) {
                String unionId = migrateRelationRs.getString("UNI_SEL_ID");
                if (migrateConf.containsKey(unionId)) {
                    migrateConf.get(unionId)
                            .put(migrateRelationRs.getString("ENG_FLD").toUpperCase(),
                                    migrateRelationRs.getString("FLD_COD").toUpperCase());
                } else {
                    Map<String, String> oneUnionConf = new HashMap<>();
                    oneUnionConf.put("LOAD_COD", migrateRelationRs.getString("LOAD_COD"));
                    oneUnionConf.put(migrateRelationRs.getString("ENG_FLD").toUpperCase(),
                            migrateRelationRs.getString("FLD_COD").toUpperCase());
                    migrateConf.put(unionId, oneUnionConf);
                }
            }
            String dml = "";
            for (Map.Entry<String, Map<String, String>> entry : migrateConf.entrySet()) {
                Map<String, String> oneUnionConf = entry.getValue();
                dml = dml.concat(" UNION ALL SELECT ");
                for (String col : sinkTableColumnList) {
                    String targetFiled = oneUnionConf.get(col);
                    if (StringUtils.isBlank(targetFiled)) {
                        continue;
                    }
                    dml = dml.concat(targetFiled).concat(",");
                }
                dml = dml.substring(0, dml.length() - 1).concat(" ").concat(oneUnionConf.get("LOAD_COD"));
            }
            dml = "INSERT INTO ".concat(sinkTable).concat(dml.substring(6));
            return dml;
        } catch (SQLException | ClassNotFoundException e) {
            logger.error(e.getLocalizedMessage());
            return null;
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    logger.error(e.getLocalizedMessage());
                }
            }
        }
    }

    @Override
    public List<SyncSqlDto> queryDealAllSyncSql(Integer isSuse) {
        List<RiskL1SyncSqlEntity> appendAllSqls = riskL1SyncSqlConfDao.queryDealAllSyncSql(isSuse);
        if (CollectionUtils.isEmpty(appendAllSqls)) {
            return Collections.EMPTY_LIST;
        }
        List<SyncSqlDto> list = Lists.newArrayListWithExpectedSize(appendAllSqls.size());
        SyncSqlDto syncSqlDto = null;
        for (RiskL1SyncSqlEntity entity : appendAllSqls) {
            syncSqlDto = new SyncSqlDto();
            syncSqlDto.setSrcTableName(entity.getSourceTableName());
            syncSqlDto.setTgtTableName(entity.getTargetTableName());
            syncSqlDto.setDealPrio(entity.getDealPrio());
            syncSqlDto.setSyncSql(entity.getAppendSql());
            list.add(syncSqlDto);
        }
        return list;
    }


}
